package com.briup;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class HelloWorld {
    String driver = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://127.0.0.1:3306/briup";
    String username = "briup";
    String password = "briup";
    @Test
    public void createTable() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            int rows = stmt.executeUpdate("CREATE TABLE  t_customer(id int not null primary key,name varchar(20) not null,age int,birthday date,register_time date)");
            System.out.println(rows);
        } catch (Exception e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }
    @Test
    public void intoTable() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            int rows = stmt.executeUpdate("INSERT INTO t_customer VALUES (001,'甄',44,'1988.1.5','2024.1.1')");
            rows = stmt.executeUpdate("INSERT INTO t_customer VALUES (002,'甄小',22,'2003.1.5','2024.1.2')");
            rows = stmt.executeUpdate("INSERT INTO t_customer VALUES (003,'甄小小',27,'1997.1.5','2024.1.3')");
            rows = stmt.executeUpdate("INSERT INTO t_customer VALUES (004,'甄小小小',20,'2004.1.5','2024.1.4')");
            rows = stmt.executeUpdate("INSERT INTO t_customer VALUES (005,'甄小小小小',25,'1988.1.5','2024.1.5')");
            rows = stmt.executeUpdate("INSERT INTO t_customer VALUES (006,'甄小小小小小',24,'2000.1.5','2024.1.6')");
            System.out.println("返回结果rows：" + rows);
        } catch (Exception e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }
    //查询表
    @Test
    public void selectTable() {
        Connection conn = null;
        Statement stmt = null;
        List<Customer> list=new ArrayList();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            ResultSet r= stmt.executeQuery("select * from t_customer where '2000-01-01'>birthday");
            while(r.next()){
                Customer u=new Customer(r.getInt("id"),r.getString("name"),r.getInt("age"),r.getString("birthday"),r.getString("register_time"));
                list.add(u);
            }
            System.out.println(list);
            System.out.println(list.size());
        } catch (Exception e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }
}
//    public static void main(String[] args) {
//        System.out.println("hello，World");
//    }
//    @Test
//    public void test() {
//        System.out.println("hello，maven");
//    }